﻿Imports MySql.Data.MySqlClient

Public Class CSiswa
    Inherits CContext

    Private INSERT_COMMAND As String
    Private UPDATE_COMMAND As String
    Private DELETE_COMMAND As String
    Private SELECT_COMMAND As String

    Public Sub New()
        MyBase.New()
    End Sub

    Public Sub simpan(SiswaBaru As tbl_siswa)
        Dim insertCommand As New MySqlCommand
        With insertCommand
            .CommandText = "INSERT INTO simas_db.tbl_siswa(nisn,nama,tmpt_lhr,tgl_lhr,jk,almt,ank_ke,jml_sdr,nm_ayh,nm_ibu,almt_ortu,telp_ayh,telp_ibu,id_krj_ayh,id_krj_ibu,nm_wli,tlp_wli,almt_wli,foto,tngkt,almt_ibu)VALUES(?nisn,?nama ,?tmpt_lhr,?tgl_lhr,?jk,?almt,?ank_ke,?jml_sdr,?nm_ayh,?nm_ibu,?almt_ortu,?telp_ayh,?telp_ibu,?id_krj_ayh,?id_krj_ibu,?nm_wli,?tlp_wli,?almt_wli,?foto,?tngkt,?almt_ibu)"
            .Parameters.Add(New MySqlParameter("nisn", SiswaBaru.NISN))
            .Parameters.Add(New MySqlParameter("nama", SiswaBaru.Nama))
            .Parameters.Add(New MySqlParameter("tmpt_lhr", SiswaBaru.TempatLahir))
            .Parameters.Add(New MySqlParameter("tgl_lhr", SiswaBaru.TanggalLahir))
            .Parameters.Add(New MySqlParameter("jk", SiswaBaru.JenisKelamin))
            .Parameters.Add(New MySqlParameter("almt", SiswaBaru.Alamat))
            .Parameters.Add(New MySqlParameter("ank_ke", SiswaBaru.AnakKe))
            .Parameters.Add(New MySqlParameter("jml_sdr", SiswaBaru.JumlahSaudara))
            .Parameters.Add(New MySqlParameter("nm_ayh", SiswaBaru.NamaAyah))
            .Parameters.Add(New MySqlParameter("nm_ibu", SiswaBaru.NamaIbu))
            .Parameters.Add(New MySqlParameter("almt_ortu", SiswaBaru.AlamatAyah))
            .Parameters.Add(New MySqlParameter("telp_ayh", SiswaBaru.TelpAyah))
            .Parameters.Add(New MySqlParameter("telp_ibu", SiswaBaru.TelpIbu))
            .Parameters.Add(New MySqlParameter("id_krj_ayh", 1))
            .Parameters.Add(New MySqlParameter("id_krj_ibu", 2))
            .Parameters.Add(New MySqlParameter("nm_wli", SiswaBaru.NamaWali))
            .Parameters.Add(New MySqlParameter("tlp_wli", SiswaBaru.TelpWali))
            .Parameters.Add(New MySqlParameter("almt_wli", SiswaBaru.AlamatWali))
            .Parameters.Add(New MySqlParameter("foto", SiswaBaru.foto))
            .Parameters.Add(New MySqlParameter("tngkt", SiswaBaru.Tingkatan))
            .Parameters.Add(New MySqlParameter("almt_ibu", SiswaBaru.AlamatIbu))
        End With
        MyBase.InsertData(insertCommand)
    End Sub

    Public Sub simpan(siswabaru As tbl_siswa, kelas As tbl_kelas, semester As tbl_semester)
        Dim _TMysql As MySql.Data.MySqlClient.MySqlTransaction
        MyBase.MySqlConnect.Open()
        _TMysql = MyBase.MySqlConnect.BeginTransaction

        Try


            Dim INSERT_SISWA_COMMAND As MySql.Data.MySqlClient.MySqlCommand = MyBase.MySqlConnect.CreateCommand

            'INSERT SISWA COMMAND
            With INSERT_SISWA_COMMAND
                .CommandText = "INSERT INTO simas_db.tbl_siswa(nisn,nama,tmpt_lhr,tgl_lhr,jk,almt,ank_ke,jml_sdr,nm_ayh,nm_ibu,almt_ortu,telp_ayh,telp_ibu,id_krj_ayh,id_krj_ibu,nm_wli,tlp_wli,almt_wli,foto,tngkt,almt_ibu)VALUES(?nisn,?nama ,?tmpt_lhr,?tgl_lhr,?jk,?almt,?ank_ke,?jml_sdr,?nm_ayh,?nm_ibu,?almt_ortu,?telp_ayh,?telp_ibu,?id_krj_ayh,?id_krj_ibu,?nm_wli,?tlp_wli,?almt_wli,?foto,?tngkt,?almt_ibu)"
                .Parameters.Add(New MySqlParameter("nisn", siswabaru.nisn))
                .Parameters.Add(New MySqlParameter("nama", siswabaru.nama))
                .Parameters.Add(New MySqlParameter("tmpt_lhr", siswabaru.Tempatlahir))
                .Parameters.Add(New MySqlParameter("tgl_lhr", siswabaru.TanggalLahir))
                .Parameters.Add(New MySqlParameter("jk", siswabaru.JenisKelamin))
                .Parameters.Add(New MySqlParameter("almt", siswabaru.Alamat))
                .Parameters.Add(New MySqlParameter("ank_ke", siswabaru.AnakKe))
                .Parameters.Add(New MySqlParameter("jml_sdr", siswabaru.JumlahSaudara))
                .Parameters.Add(New MySqlParameter("nm_ayh", siswabaru.NamaAyah))
                .Parameters.Add(New MySqlParameter("nm_ibu", siswabaru.NamaIbu))
                .Parameters.Add(New MySqlParameter("almt_ortu", siswabaru.AlamatAyah))
                .Parameters.Add(New MySqlParameter("telp_ayh", siswabaru.TelpAyah))
                .Parameters.Add(New MySqlParameter("telp_ibu", siswabaru.TelpIbu))
                .Parameters.Add(New MySqlParameter("id_krj_ayh", 1))
                .Parameters.Add(New MySqlParameter("id_krj_ibu", 2))
                .Parameters.Add(New MySqlParameter("nm_wli", siswabaru.NamaWali))
                .Parameters.Add(New MySqlParameter("tlp_wli", siswabaru.TelpWali))
                .Parameters.Add(New MySqlParameter("almt_wli", siswabaru.AlamatWali))
                .Parameters.Add(New MySqlParameter("foto", siswabaru.foto))
                .Parameters.Add(New MySqlParameter("tngkt", siswabaru.Tingkatan))
                .Parameters.Add(New MySqlParameter("almt_ibu", siswabaru.AlamatIbu))

                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            'SELECT ID SISWA BARU
            Dim SELECT_ID_SISWA_COMMAND As MySqlCommand = MyBase.MySqlConnect.CreateCommand
            Dim reader As MySqlDataReader
            Dim idSiswa As Integer
            With SELECT_ID_SISWA_COMMAND
                .CommandText = "select max(id) from tbl_siswa"
                .Transaction = _TMysql
                reader = .ExecuteReader
            End With
            reader.Read()
            idSiswa = reader.Read
            reader.Close()

            'INSERT PENEMPATAN KELAS
            Dim INSERT_KELAS_COMMAND As MySqlCommand = MyBase.MySqlConnect.CreateCommand
            With INSERT_KELAS_COMMAND
                .CommandText = "insert into tbl_pembagian_kelas (id_smstr,id_kls,id_ssw) values (?id_smstr,?id_kls,?id_ssw)"
                .Parameters.Add(New MySqlParameter("id_smstr", semester.id))
                .Parameters.Add(New MySqlParameter("id_kls", kelas.id))
                .Parameters.Add(New MySqlParameter("id_ssw", idSiswa))

                .Transaction = _TMysql
                .ExecuteNonQuery()
            End With

            'commit command
            _TMysql.Commit()
        Catch ex As Exception
            _TMysql.Rollback()
            MsgBox(ex.Message)
        Finally
            MyBase.MySqlConnect.Close()
        End Try
    End Sub

    Public Sub simpan(ByVal nisn As String, ByVal nama As String, ByVal tempatLahir As String, ByVal tglLahir As String, ByVal jenisKelamin As Char, ByVal alamat As String, ByVal anakKe As String, ByVal jumlahSaudara As String, ByVal namaAyah As String, ByVal namaIbu As String, ByVal alamatOrtu As String, ByVal telpAyah As String, ByVal telpIbu As String, ByVal idKerjaAyah As String, ByVal idKerjaIbu As String, ByVal namaWali As String, ByVal telpWali As String, ByVal alamatWali As String, ByVal foto As String)

        Dim insertCommand As New MySqlCommand
        With insertCommand

            .CommandText = "CALL SP_SIMPAN_SISWA(?NISN,?NAMA,?TMPT_LHR,?TGL_LHR,?JK,?ALMT,?ANK_KE,?JML_SDR,?NM_AYH,?NM_IBU,?ALMT_ORTU,?TELP_AYH,?TELP_IBU,?ID_KRJ_AYH,?ID_KRJ_IBU,?NM_WLI,?TLP_WLI,?ALMT_WLI,?FOTO)"
            .Parameters.Add(New MySqlParameter("NISN", nisn))
            .Parameters.Add(New MySqlParameter("NAMA", nama))
            .Parameters.Add(New MySqlParameter("TMPT_LHR", tempatLahir))
            .Parameters.Add(New MySqlParameter("TGL_LHR", tglLahir))
            .Parameters.Add(New MySqlParameter("JK", jenisKelamin))
            .Parameters.Add(New MySqlParameter("ALMT", alamat))
            .Parameters.Add(New MySqlParameter("ANK_KE", anakKe))
            .Parameters.Add(New MySqlParameter("JML_SDR", jumlahSaudara))
            .Parameters.Add(New MySqlParameter("NM_AYH", namaAyah))
            .Parameters.Add(New MySqlParameter("NM_IBU", namaIbu))
            .Parameters.Add(New MySqlParameter("ALMT_ORTU", alamatOrtu))
            .Parameters.Add(New MySqlParameter("TELP_AYH", telpAyah))
            .Parameters.Add(New MySqlParameter("TELP_IBU", telpIbu))
            .Parameters.Add(New MySqlParameter("ID_KRJ_AYH", idKerjaAyah))
            .Parameters.Add(New MySqlParameter("ID_KRJ_IBU", idKerjaIbu))
            .Parameters.Add(New MySqlParameter("NM_WLI", namaWali))
            .Parameters.Add(New MySqlParameter("TLP_WLI", telpWali))
            .Parameters.Add(New MySqlParameter("ALMT_WLI", alamatWali))
            .Parameters.Add(New MySqlParameter("FOTO", foto))
        End With
        MyBase.InsertData(insertCommand)
    End Sub

    Public Sub rubah(ByVal id As Integer, ByVal nisn As String, ByVal nama As String, ByVal tempatLahir As String, ByVal tglLahir As String, ByVal jenisKelamin As Char, ByVal alamat As String, ByVal anakKe As String, ByVal jumlahSaudara As String, ByVal namaAyah As String, ByVal namaIbu As String, ByVal alamatOrtu As String, ByVal telpAyah As String, ByVal telpIbu As String, ByVal idKerjaAyah As String, ByVal idKerjaIbu As String, ByVal namaWali As String, ByVal telpWali As String, ByVal alamatWali As String, ByVal foto As String)

        Dim updateCommand As New MySqlCommand
        With updateCommand

            .CommandText = "CALL SP_RUBAH_SISWA(?ID,?NISN,?NAMA,?TMPT_LHR,?TGL_LHR,?JK,?ALMT,?ANK_KE,?JML_SDR,?NM_AYH,?NM_IBU,?ALMT_ORTU,?TELP_AYH,?TELP_IBU,?ID_KRJ_AYH,?ID_KRJ_IBU,?NM_WLI,?TLP_WLI,?ALMT_WLI,?FOTO)"
            .Parameters.Add(New MySqlParameter("ID", id))
            .Parameters.Add(New MySqlParameter("NISN", nisn))
            .Parameters.Add(New MySqlParameter("NAMA", nama))
            .Parameters.Add(New MySqlParameter("TMPT_LHR", tempatLahir))
            .Parameters.Add(New MySqlParameter("TGL_LHR", tglLahir))
            .Parameters.Add(New MySqlParameter("JK", jenisKelamin))
            .Parameters.Add(New MySqlParameter("ALMT", alamat))
            .Parameters.Add(New MySqlParameter("ANK_KE", anakKe))
            .Parameters.Add(New MySqlParameter("JML_SDR", jumlahSaudara))
            .Parameters.Add(New MySqlParameter("NM_AYH", namaAyah))
            .Parameters.Add(New MySqlParameter("NM_IBU", namaIbu))
            .Parameters.Add(New MySqlParameter("ALMT_ORTU", alamatOrtu))
            .Parameters.Add(New MySqlParameter("TELP_AYH", telpAyah))
            .Parameters.Add(New MySqlParameter("TELP_IBU", telpIbu))
            .Parameters.Add(New MySqlParameter("ID_KRJ_AYH", idKerjaAyah))
            .Parameters.Add(New MySqlParameter("ID_KRJ_IBU", idKerjaIbu))
            .Parameters.Add(New MySqlParameter("NM_WLI", namaWali))
            .Parameters.Add(New MySqlParameter("TLP_WLI", telpWali))
            .Parameters.Add(New MySqlParameter("ALMT_WLI", alamatWali))
            .Parameters.Add(New MySqlParameter("FOTO", foto))
        End With
        MyBase.UpdateData(updateCommand)

    End Sub

    Public Sub hapus(ByVal idSiswa As Integer)
        'validasi
        If idSiswa > 0 Then
            Me.DELETE_COMMAND = "DELETE FROM TBL_SISWA WHERE ID=" & idSiswa
            MyBase.DeleteData(DELETE_COMMAND)
        Else
            Throw New Exception
        End If
    End Sub

    Public Function getAll() As DataTable
        Me.SELECT_COMMAND = "SELECT * FROM TBL_SISWA"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getById(ByVal idSiswa As Integer) As DataTable
        Me.SELECT_COMMAND = "CALL SP_DETIL_SISWA(" & idSiswa & ")"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getTempatLahirSiswa() As DataTable
        Me.SELECT_COMMAND = "select distinct(tmpt_lhr) from tbl_siswa"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getSiswaBaru() As DataTable
        Me.SELECT_COMMAND = "SELECT * FROM TBL_SISWA where tngkt='b'"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function

    Public Function getDisplay() As DataTable
        Me.SELECT_COMMAND = "CALL SP_MASTER_SISWA"
        Return MyBase.SelectData(SELECT_COMMAND)
    End Function


End Class
